﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
using System.Data.SqlClient;

namespace ElingOA6_Admin_DSJCJ.DAL
{
    public class SysTemplate
    {

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public ElingOA6_Admin_DSJCJ.Model.SysTemplate DataRowToModel(DataRow row)
        {
            ElingOA6_Admin_DSJCJ.Model.SysTemplate model = new ElingOA6_Admin_DSJCJ.Model.SysTemplate();
            if (row != null)
            {
                if (row["FTemplateID"] != null && row["FTemplateID"].ToString() != "")
                {
                    model.FTemplateID = decimal.Parse(row["FTemplateID"].ToString());
                }
                if (row["FEntityNo"] != null)
                {
                    model.FEntityNo = row["FEntityNo"].ToString();
                }
                if (row["FTemplateCls"] != null)
                {
                    model.FTemplateCls = row["FTemplateCls"].ToString();
                }
                if (row["FSourceNo"] != null)
                {
                    model.FSourceNo = row["FSourceNo"].ToString();
                }
                if (row["FTemplateType"] != null)
                {
                    model.FTemplateType = row["FTemplateType"].ToString();
                }
                if (row["FTitle"] != null)
                {
                    model.FTitle = row["FTitle"].ToString();
                }
                if (row["FContent"] != null && row["FContent"].ToString() != "")
                {
                    model.FContent = (byte[])row["FContent"];
                }
                if (row["FFileName"] != null)
                {
                    model.FFileName = row["FFileName"].ToString();
                }
                if (row["FIsDefault"] != null)
                {
                    model.FIsDefault = row["FIsDefault"].ToString();
                }
            }
            return model;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(decimal FTemplateID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from t_SysTemplate ");
            strSql.Append(" where FTemplateID=@FTemplateID");
            SqlParameter[] parameters = {
					new SqlParameter("@FTemplateID", SqlDbType.Decimal)
			};
            parameters[0].Value = FTemplateID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool DeleteList(string idList)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from t_SysTemplate ");
            strSql.Append(" where FTemplateID in (" + idList + ")");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 获取文件路径
        /// <creator>zhongyl</creator>
        /// <date>2013-07-18</date>
        /// </summary>
        /// <param name="FTemplateID"></param>
        /// <returns></returns>
        public string GetFileName(decimal FTemplateID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 FFileName from t_SysTemplate ");
            strSql.Append(" where FTemplateID=@FTemplateID");
            SqlParameter[] parameters = {
					new SqlParameter("@FTemplateID", SqlDbType.Decimal)};
            parameters[0].Value = FTemplateID;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (null != obj)
            {
                return obj.ToString();
            }
            else
            {
                return string.Empty;
            }
        }


        /// <summary>
        /// 获取模板内容
        /// <creator>zhongyl</creator>
        /// <date>2013-01-23</date>
        /// </summary>
        /// <param name="FTemplateID"></param>
        /// <returns></returns>
        public string GetContent(decimal FTemplateID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 FContent from t_SysTemplate ");
            strSql.Append(" where FTemplateID=@FTemplateID");
            SqlParameter[] parameters = {
					new SqlParameter("@FTemplateID", SqlDbType.Decimal)};
            parameters[0].Value = FTemplateID;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (null != obj)
            {
                return System.Text.Encoding.Default.GetString(obj as byte[]);
            }
            else
            {
                return string.Empty;
            }
        }
        /// <summary>
        /// 获得非空的文件路径
        /// <creator>wagnl</creator>
        /// <date>2013-08-02</date>
        /// </summary>
        /// <param name="entityNo">公司编号</param>
        /// <param name="sourceNo">对应编码</param>
        /// <returns></returns>
        public List<string> GetFileNames(string entityNo, string sourceNo)
        {
            List<string> listStr = new List<string>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FFileName from t_SysTemplate ");
            strSql.Append(" where FEntityNo=@FEntityNo and FSourceNo=@FSourceNo and  FTemplateCls='DOC' and FFileName!='' and FFileName is not null");
            SqlParameter[] parameters = {					
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),					
					new SqlParameter("@FSourceNo", SqlDbType.NVarChar,25)
					};
            parameters[0].Value = entityNo;
            parameters[1].Value = sourceNo;

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                listStr.Add(ds.Tables[0].Rows[i]["FFileName"].ToString());
            }

            return listStr;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public ElingOA6_Admin_DSJCJ.Model.SysTemplate GetModel(decimal FTemplateID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 FTemplateID,FEntityNo,FTemplateCls,FSourceNo,FTemplateType,FTitle,FContent,FFileName,FIsDefault from t_SysTemplate ");
            strSql.Append(" where FTemplateID=@FTemplateID");
            SqlParameter[] parameters = {
					new SqlParameter("@FTemplateID", SqlDbType.Decimal)
			};
            parameters[0].Value = FTemplateID;

            ElingOA6_Admin_DSJCJ.Model.SysTemplate model = new ElingOA6_Admin_DSJCJ.Model.SysTemplate();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FTemplateID,FEntityNo,FTemplateCls,FSourceNo,FTemplateType,FTitle,FContent,FFileName,FIsDefault ");
            strSql.Append(" FROM t_SysTemplate ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public void Add(ElingOA6_Admin_DSJCJ.Model.SysTemplate model)
        {
            List<CommandInfo> cmdList = new List<CommandInfo>();
            CommandInfo cmds = new CommandInfo();
            StringBuilder strSql = new StringBuilder();
            if (model.FIsDefault == "Y")
            {
                strSql = new StringBuilder();
                cmds = new CommandInfo();
                strSql.Append("update t_SysTemplate set ");
                strSql.Append("FIsDefault='N'");
                strSql.Append(" where FSourceNo=@FSourceNo and FTemplateType=@FTemplateType");
                SqlParameter[] parameters2 = {
					new SqlParameter("@FSourceNo", SqlDbType.NVarChar,25),
                    new SqlParameter("@FTemplateType", SqlDbType.Char,1),};
                parameters2[0].Value = model.FSourceNo;
                parameters2[1].Value = model.FTemplateType;
                cmds.CommandText = strSql.ToString();
                cmds.Parameters = parameters2;
                cmdList.Add(cmds);
            }

            strSql = new StringBuilder();
            cmds = new CommandInfo();
            strSql.Append("insert into t_SysTemplate(");
            strSql.Append("FEntityNo,FTemplateCls,FSourceNo,FTemplateType,FTitle,FContent,FFileName,FIsDefault)");
            strSql.Append(" values (");
            strSql.Append("@FEntityNo,@FTemplateCls,@FSourceNo,@FTemplateType,@FTitle,@FContent,@FFileName,@FIsDefault)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FTemplateCls", SqlDbType.NVarChar,20),
					new SqlParameter("@FSourceNo", SqlDbType.NVarChar,25),
					new SqlParameter("@FTemplateType", SqlDbType.Char,1),
					new SqlParameter("@FTitle", SqlDbType.NVarChar,200),
					new SqlParameter("@FContent", SqlDbType.Image),
					new SqlParameter("@FFileName", SqlDbType.NVarChar,255),
					new SqlParameter("@FIsDefault", SqlDbType.Char,1)};
            parameters[0].Value = model.FEntityNo;
            parameters[1].Value = model.FTemplateCls;
            parameters[2].Value = model.FSourceNo;
            parameters[3].Value = model.FTemplateType;
            parameters[4].Value = model.FTitle;
            parameters[5].Value = model.FContent;
            parameters[6].Value = model.FFileName;
            parameters[7].Value = model.FIsDefault;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);


            DbHelperSQL.ExecuteSqlTranWithString(cmdList, 1800);
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public void Update(ElingOA6_Admin_DSJCJ.Model.SysTemplate model)
        {
            List<CommandInfo> cmdList = new List<CommandInfo>();
            CommandInfo cmds = new CommandInfo();
            StringBuilder strSql = new StringBuilder();
            if (model.FIsDefault == "Y")
            {
                strSql = new StringBuilder();
                cmds = new CommandInfo();
                strSql.Append("update t_SysTemplate set ");
                strSql.Append("FIsDefault='N'");
                strSql.Append(" where FSourceNo=@FSourceNo and FTemplateType=@FTemplateType");
                SqlParameter[] parameters2 = {
					new SqlParameter("@FSourceNo", SqlDbType.NVarChar,25),
                    new SqlParameter("@FTemplateType", SqlDbType.Char,1),};
                parameters2[0].Value = model.FSourceNo;
                parameters2[1].Value = model.FTemplateType;
                cmds.CommandText = strSql.ToString();
                cmds.Parameters = parameters2;
                cmdList.Add(cmds);
            }

            strSql = new StringBuilder();
            cmds = new CommandInfo();
            strSql.Append("update t_SysTemplate set ");
            strSql.Append("FTemplateCls=@FTemplateCls,");
            strSql.Append("FSourceNo=@FSourceNo,");
            strSql.Append("FTemplateType=@FTemplateType,");
            strSql.Append("FTitle=@FTitle,");
            strSql.Append("FContent=@FContent,");
            strSql.Append("FFileName=@FFileName,");
            strSql.Append("FIsDefault=@FIsDefault");
            strSql.Append(" where FTemplateID=@FTemplateID");
            SqlParameter[] parameters = {
					new SqlParameter("@FTemplateCls", SqlDbType.NVarChar,20),
					new SqlParameter("@FSourceNo", SqlDbType.NVarChar,25),
					new SqlParameter("@FTemplateType", SqlDbType.Char,1),
					new SqlParameter("@FTitle", SqlDbType.NVarChar,200),
					new SqlParameter("@FContent", SqlDbType.Image),
					new SqlParameter("@FFileName", SqlDbType.NVarChar,255),
					new SqlParameter("@FIsDefault", SqlDbType.Char,1),
					new SqlParameter("@FTemplateID", SqlDbType.Decimal,9),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12)};
            parameters[0].Value = model.FTemplateCls;
            parameters[1].Value = model.FSourceNo;
            parameters[2].Value = model.FTemplateType;
            parameters[3].Value = model.FTitle;
            parameters[4].Value = model.FContent;
            parameters[5].Value = model.FFileName;
            parameters[6].Value = model.FIsDefault;
            parameters[7].Value = model.FTemplateID;
            parameters[8].Value = model.FEntityNo;

            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);


            DbHelperSQL.ExecuteSqlTranWithString(cmdList, 1800);
        }
        /// <summary>
        /// 获取模板数据
        /// <creator>wangl</creator>
        /// <date>2013-08-07</date>
        /// </summary>
        /// <param name="entityNo">公司编号</param>
        /// <param name="sourceNo">来源号</param>
        /// <param name="templateType">模板类型</param>
        /// <returns>DataSet对象</returns>
        public DataSet GetListByTemplateType(string entityNo, string sourceNo, string templateType)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM t_SysTemplate  where FTemplateCls='DOC' and FEntityNo=@FEntityNo and FSourceNo=@FSourceNo");
            if (templateType != "A")
            {
                strSql.Append(" and FTemplateType=@FTemplateType");
            }
            else
            {
                strSql.Append(" and FTemplateType!=@FTemplateType");
            }
            SqlParameter[] parameters = {                    
                    new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),                   
                    new SqlParameter("@FSourceNo", SqlDbType.NVarChar,25),
                    new SqlParameter("@FTemplateType", SqlDbType.Char,1),
                 };
            parameters[0].Value = entityNo;
            parameters[1].Value = sourceNo;
            parameters[2].Value = templateType;

            return DbHelperSQL.Query(strSql.ToString(), parameters);

        }
    }
}
